# Copyright (C) 2026. Logiciel élaboré par l'État, via le DEPS.
# Nom des auteures : Flore Cornuet et Camille Parent, DEPS.
# Ce programme informatique a été développé par le DEPS.
# Il permet de produire les résultats du Culture Études 1-2026.

# Ce programme a été exécuté en septembre 2026 avec la version 4.5.3 de R.
# La publication peut être consultée sur le site du Deps. 
# Ce programme utilise les données du pass Culture et du recensement de la population 2022.

# Bien qu'il n'existe aucune obligation légale à ce sujet, les utilisateurs de ce programme sont
# invités à signaler au DEPS leurs travaux issus de la réutilisation de ce code, ainsi que les
# éventuels problèmes ou anomalies qu'ils y rencontreraient, en écrivant à contact.deps@culture.gouv.fr .
# Ce logiciel est régi par la licence EUPL V1.2 soumise au droit français et européen, respectant
# les principes de diffusion des logiciels libres. Vous pouvez utiliser, modifier et/ou redistribuer
# ce programme sous les conditions de la licence EUPL V1.2, telle que diffusée sur
# https://eupl.eu/1.2/fr/ .
# En contrepartie de l'accessibilité au code source et des droits de copie, de modification et de
# redistribution accordés par cette licence, il n'est offert aux utilisateurs qu'une garantie
# limitée. Pour les mêmes raisons, seule une responsabilité restreinte pèse sur les auteures du
# programme, le titulaire des droits patrimoniaux et les concédants successifs.
# À cet égard l'attention de l'utilisateur est attirée sur les risques associés au chargement, à
# l'utilisation, à la modification et/ou au développement et à la reproduction du logiciel par
# l'utilisateur étant donné sa spécificité de logiciel libre, qui peut le rendre complexe à
# manipuler et qui le réserve donc à des développeurs et des professionnels avertis possédant des
# connaissances informatiques approfondies. Les utilisateurs sont donc invités à charger et tester
# l'adéquation du logiciel à leurs besoins dans des conditions permettant d'assurer la sécurité de
# leurs systèmes et ou de leurs données et, plus généralement, à l'utiliser et l'exploiter dans les
# mêmes conditions de sécurité.



# chargement des packages ####

library(data.table); library(tidyverse); library(glue)
library(arrow); library(duckdb); library(DBI); library(readxl); library(kableExtra)


# création de la connexion duckdb ####

con <- dbConnect(duckdb())
dbExecute(con, "SET threads = 4")
dbExecute(con, "SET preserve_insertion_order = false")
dbExecute(con, "SET memory_limit = '16GB'")

# === Table de passage codegeo -> densité ===

tab_densite <- read_xlsx(
  "C:/Users/camille.parent/Documents/Dev/pass_culture/data/correspondance_codegeo_densite_urbaine_insee2026.xlsx",
  skip = 4
)
duckdb_register(con, "tab_dens", tab_densite)

# =====================================================================
# NUMÉRATEUR : bénéficiaires chq300 (champ création), nés 2005, par densité
# =====================================================================
benef_dens <- dbGetQuery(con, glue("
  WITH addr_uniq AS (
    SELECT ad.user_id, ANY_VALUE(d.LIBDENS7) AS LIBDENS7
    FROM (
      SELECT ua.user_id,
             CASE
               WHEN gi.city_code BETWEEN '75101' AND '75120' THEN '75056'
               WHEN gi.city_code BETWEEN '13201' AND '13216' THEN '13055'
               WHEN gi.city_code BETWEEN '69381' AND '69389' THEN '69123'
               ELSE gi.city_code
             END AS code_commune
      FROM read_parquet('{chemin_addr}') ua
      LEFT JOIN read_parquet('{chemin_iris}') gi
        ON ua.user_iris_internal_id = gi.iris_internal_id
    ) ad
    LEFT JOIN tab_dens d ON ad.code_commune = d.CODGEO
    GROUP BY ad.user_id
  ),
  benef AS (
    SELECT DISTINCT dep.user_id
    FROM read_parquet('{chemin_deposit}') dep
    JOIN read_parquet('{chemin_user}') u ON dep.user_id = u.user_id
    WHERE dep.deposit_amount = 300
      AND dep.deposit_creation_date >= '2021-05-21'
      AND dep.deposit_creation_date <  '2025-02-28'
      AND EXTRACT(YEAR FROM u.user_birth_date) = 2005
  )
  SELECT a.LIBDENS7,
         COUNT(DISTINCT b.user_id) AS nb_benef
  FROM benef b
  LEFT JOIN addr_uniq a ON b.user_id = a.user_id
  GROUP BY a.LIBDENS7
"))
setDT(benef_dens)

# =====================================================================
# DÉNOMINATEUR : 17 ans du RP 2022 (nés 2005) par densité
# =====================================================================
pop <- setDT(read_xlsx(paste0(getwd(), "/data/TD_POP1B_2022.xlsx"),
                       skip = 10, sheet = 1))
pop[, age_17 := F_17 + H_17]
pop <- pop[, .(codgeo = CODGEO, age_17)]
duckdb_register(con, "pop", pop)

pop_dens <- dbGetQuery(con, "
  SELECT d.LIBDENS7,
         SUM(p.age_17) AS pop_2005
  FROM pop p
  LEFT JOIN tab_dens d ON p.codgeo = d.CODGEO
  GROUP BY d.LIBDENS7
")
setDT(pop_dens)

# =====================================================================
# TAUX DE RECOURS
# =====================================================================
taux <- merge(benef_dens[!is.na(LIBDENS7)],
              pop_dens[!is.na(LIBDENS7)],
              by = "LIBDENS7")
taux[, taux_recours := round(nb_benef / pop_2005 * 100, 1)]

ordre_dens7 <- c(
  "Grands centres urbains","Centres urbains intermédiaires","Ceintures urbaines",
  "Petites villes","Bourgs ruraux","Rural à habitat dispersé","Rural à habitat très dispersé"
)
taux <- taux[order(match(LIBDENS7, ordre_dens7))]
taux

# === kableExtra ===
taux |>
  kbl(
    caption   = "Taux de recours au chèque de 300 € des jeunes nés en 2005, selon la densité urbaine",
    col.names = c("Catégorie de densité", "Bénéficiaires", "Population (17 ans, RP 2022)", "Taux de recours (%)"),
    align     = c("l","c","c","c"),
    format.args = list(big.mark = " ")
  ) |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"),
                full_width = FALSE, position = "center") |>
  footnote(
    general = paste0(
      "Champ : chèques de 300 euros créés entre le 21 mai 2021 et le 28 février 2025. ",
      "Dénominateur : population âgée de 17 ans au recensement de la population 2022, ",
      "répartie par commune de résidence. ",
      "Source : application pass Culture ; Insee, recensement de la population 2022."
    ),
    general_title = "", footnote_as_chunk = TRUE
  )

write.table(taux, "clipboard", sep = "\t", row.names = FALSE, dec = ",")
